package com.yulian.repository.system_management;

import java.util.Iterator;

import com.healthmarketscience.sqlbuilder.BinaryCondition;
import com.healthmarketscience.sqlbuilder.CustomSql;
import com.healthmarketscience.sqlbuilder.InCondition;
import com.healthmarketscience.sqlbuilder.SelectQuery;

import io.netty.util.internal.logging.InternalLogger;
import io.netty.util.internal.logging.Log4JLoggerFactory;
import io.vertx.core.Future;
import io.vertx.core.Promise;
import io.vertx.core.json.JsonObject;
import io.vertx.sqlclient.Row;
import io.vertx.sqlclient.RowSet;
import io.vertx.sqlclient.SqlConnection;
import io.vertx.sqlclient.Tuple;
/**
 * 系统数据接口权限控制的数据访问层类，在该类的多个方法中，执行操作数据的sql语句；
 * 实现权限菜单的增、删、查、改等功能，为业务逻辑层中的类AuthorationManager提供数据服务。
 * @author 余大勇
 *
 */
public class AuthorizationRepositoryImpl {
	
	final InternalLogger logger = Log4JLoggerFactory.getInstance(AuthorizationRepositoryImpl.class);
	
	
	public Future<RowSet<Row>> getAuth(SqlConnection conn,int user_id, String parent_name){
		Promise<RowSet<Row>> promise = Promise.promise();   
		String sql = "select distinct b.menu_code, b.menu_name from roles_perms a left join system_menu b \n"
				+ "on ( a.perm_code= b.menu_code ) where a.role_code = any(array( select role_code from users_roles c left join users d on ( c.user_code = d.user_code )  where d.id = $1)) and b.parent_name= $2" ;  
		conn
	    .preparedQuery(sql)
	    .execute(Tuple.of(user_id, parent_name), ar2 -> {
	    	//Release the connection to the pool
	    	conn.close();
	        if(ar2.succeeded()){
	        	logger.info("getAuth, 记录数: {} ",  ar2.result().rowCount());
	        	promise.complete(ar2.result());
	        }else{
	        	logger.info("getAuth, 失败,失败原因:{}", ar2.cause().getMessage());
	        	//用Promise来向上抛异常
	        	promise.fail(ar2.cause());
	        }
	    });
	    return promise.future();
	}
	/**
	 * 取出当前角色具有权限的菜单
	 * @param conn
	 * @param queryJSObj
	 * @return
	 */
	public Future<RowSet<Row>> getRecords(SqlConnection conn, JsonObject queryJSObj){
		Promise<RowSet<Row>> promise = Promise.promise();   
		String sql = this.creaStrGetRecordsWithJson(queryJSObj) ;  
		conn
	    .preparedQuery(sql)
	    .execute( ar2 -> {
	    	//Release the connection to the pool
	    	conn.close();
	        if(ar2.succeeded()){
	        	logger.info("getRecords, 记录数: {} ",  ar2.result().rowCount());
	        	promise.complete(ar2.result());
	        }else{
	        	logger.info("getRecords, 失败,失败原因:{}", ar2.cause().getMessage());
	        	//用Promise来向上抛异常
	        	promise.fail(ar2.cause());
	        }
	    });
	    return promise.future();
	}
	
	/**
	 * 此方法创建sql语句,在payment_access表中查询符合搜索条件的所有记录
	 * @param queryJS
	 * @param query_offset
	 * @param query_limit
	 * @return
	 */ 
	public String creaStrGetRecordsWithJson(JsonObject queryJS) {
		
		// 定义 SelectQuery 对象  
		SelectQuery sq = new SelectQuery(); 
		sq.addCustomColumns( new CustomSql("system_menu.id"), new CustomSql("system_menu.menu_code"), new CustomSql("menu_name"), 
				 new CustomSql("parent_code"), new CustomSql("parent_name"),  new CustomSql("menu_path"), new CustomSql("menu_icon"),
				 new CustomSql("menu_icon_open"), new CustomSql("is_leaf"), new CustomSql("is_button")  );
		  
    	 // add inner join two tables
    	 sq.addCustomJoin(SelectQuery.JoinType.LEFT_OUTER,
                 new CustomSql("system_menu"), new CustomSql("role_menus"),
                 BinaryCondition.equalTo( new CustomSql("system_menu.menu_code"), new CustomSql("role_menus.menu_code")));
	  
		//通过循环取出Json对象中的查询参数，并添加到 InCondition中 
		Iterator<String> iter =   queryJS.fieldNames().iterator();  
		while (iter.hasNext()) { 
			String key = iter.next(); 
			try { 
			  String value = queryJS.getString(key); 
			  if( !value.equals("") && !value.equals("[]") ) {
				  String firstChar = value.substring(0,1);
				  if(firstChar.equals("["))
					  value = value.substring(1,value.length()-1);
				  value = value.replace(", ", "','");   
				  //添加含InCondtion的条件的where子句
				  sq.getWhereClause().addCustomCondition(new InCondition( new CustomSql(key),  value));
			  } 
	
			} catch (Exception e) { 
				logger.info("creaStrGetRecordsWithJson, 发生错误,错误原因: ",e.getMessage()); 
			}
		}   
		String sql =sq.validate().toString() + " order by  system_menu.parent_code";
		logger.info(sql);
	   	return sql ;  
	} 
	/**
	 * 取出所有的系统菜单
	 * @param conn
	 * @return
	 */
	public Future<RowSet<Row>> getSystemMenu(SqlConnection conn){
		Promise<RowSet<Row>> promise = Promise.promise();
		// 定义 SelectQuery 对象  
		SelectQuery sq = new SelectQuery(); 
		sq.addCustomColumns( new CustomSql("id"), new CustomSql("menu_code"), new CustomSql("menu_name"), 
				 new CustomSql("parent_code"), new CustomSql("parent_name"),  new CustomSql("menu_path"), new CustomSql("menu_icon"),
				 new CustomSql("menu_icon_open"),  new CustomSql("is_leaf"), new CustomSql("is_button")  )
		.addCustomFromTable("system_menu");
		String sql =  sq.validate().toString() + " order by parent_code "; 
		logger.info("sql: {}", sql);
		conn
	    .preparedQuery(sql)
	    .execute( ar2 -> {
	    	//Release the connection to the pool
	    	conn.close();
	        if(ar2.succeeded()){
	        	logger.info("getSystemMenu, 记录数: {} ",  ar2.result().rowCount());
	        	promise.complete(ar2.result());
	        }else{
	        	logger.info("getSystemMenu, 失败,失败原因:{}", ar2.cause().getMessage());
	        	//用Promise来向上抛异常
	        	promise.fail(ar2.cause());
	        }
	    });
	    return promise.future();
	}
	
	/**
	 * 取出某个角色已经有权限的菜单code
	 * @param conn
	 * @return
	 */
	public Future<RowSet<Row>> getAssignedMenu(SqlConnection conn, String role_code){
		Promise<RowSet<Row>> promise = Promise.promise(); 
		String sql =  "select * from role_menus where role_code = $1";  
		conn
	    .preparedQuery(sql)
	    .execute(Tuple.of(role_code), ar2 -> {
	    	//Release the connection to the pool
	    	conn.close();
	        if(ar2.succeeded()){
	        	logger.info("getAssignedMenu, 记录数: {} ",  ar2.result().rowCount());
	        	promise.complete(ar2.result());
	        }else{
	        	logger.info("getAssignedMenu, 失败,失败原因:{}", ar2.cause().getMessage());
	        	//用Promise来向上抛异常
	        	promise.fail(ar2.cause());
	        }
	    });
	    return promise.future();
	}
	
	/**
	 * 1.先在角色-菜单role_menus表中删除当前角色具有权限的菜单编码menu_code;
	 * 2.再添加新的菜单编码menu_code数据
	 * 3.在角色-权限roles_perms表中删除当前角色具有权限编码perm_code; 	
	 * 4.向角色-权限roles_perms表中添加多条编码perm_code; 
	 * @param conn
	 * @param role_code
	 * @param menu_code_list
	 * @param update_user
	 * @return
	 */
	public Future<RowSet<Row>> updateAuthorization(SqlConnection conn, String role_code, String menu_code_list, String update_user){
		Promise<RowSet<Row>> promise = Promise.promise(); 
		logger.info(menu_code_list);
		//构建插入role_menus表的menu_code_string
		String menu_code_string="";
		String firstChar = menu_code_list.substring(0, 1); 
		if (firstChar.equals("["))
			menu_code_string = menu_code_list.substring(1, menu_code_list.length() - 1);
		menu_code_string = "'"+menu_code_string+"'";
		//logger.info("menu_code_string, {}",menu_code_string); 
	
		// 需要用到事务控制 
		//删除角色在 role_menus中的数据
		String sql1 = "delete from role_menus where role_code = $1";  
		//新增角色在 role_menus中的数据
		String sql2 = "insert into role_menus (role_code, menu_code) SELECT $1, unnest(string_to_array("+menu_code_string+",', ')) "; 
		//删除角色在 roles_perms中的数据
		String sql3 = "delete from roles_perms where role_code = $1";
		//构建用于递归取出所有菜单code的语句
		menu_code_list = menu_code_list.replace("[", "['");
		menu_code_list = menu_code_list.replace("]", "']");
		menu_code_list = menu_code_list.replace(", ", "','");
		logger.info(menu_code_list);
		//新增角色在roles_perms中的数据
		String sql4 = "insert into roles_perms(role_code, perm_code) (with RECURSIVE r as \n"
		+ "(select t1.id,t1.menu_code from system_menu t1 where t1.menu_code = any(array"+menu_code_list+") \n"
		+ "union all \n"
		+ "select t2.id,t2.menu_code from system_menu t2 inner join r on r.menu_code = t2.parent_code) \n"
		+ "select $1,perm_code from perms where perm_code = any(array( select menu_code from r )))"; 
		
		// Begin the transaction
		conn.begin()
		.compose(tx -> conn
				// Various statements
				.preparedQuery(sql1)
				.execute( Tuple.of(role_code) )
				.onComplete(ar1->{  //the result of the first query 
					if(ar1.succeeded()){  
						logger.info("delete_role_menus,删除数量为:" + ar1.result().rowCount()); 
			        }else{
			        	logger.info("delete_role_menus,失败,失败原因:" + ar1.cause().getMessage());	 
			        }
				})
				.compose(res2 -> conn 
						.preparedQuery(sql2)
						.execute(Tuple.of(role_code))
						.onComplete(ar2->{  //the result of the second query 
							if(ar2.succeeded()){  
								logger.info("insert_role_menus,新增数量为:" + ar2.result().rowCount());
								//promise.complete(ar2.result());
					        }else{
					        	logger.info("insert_role_menus,失败,失败原因:" + ar2.cause().getMessage());	
					        	//promise.fail(ar2.cause());
					        }
						}))  
				.compose(res3 -> conn 
						.preparedQuery(sql3)
						.execute(Tuple.of(role_code))
						.onComplete(ar2->{  //the result of the third query 
							if(ar2.succeeded()){  
								logger.info("delete_role_perms,删除数量为:" + ar2.result().rowCount()); 
					        }else{
					        	logger.info("delete_role_perms,失败,失败原因:" + ar2.cause().getMessage()); 
					        }
						})) 
				.compose(res4 -> conn 
						.preparedQuery(sql4)
						.execute(Tuple.of(role_code))
						.onComplete(ar2->{  //the result of the forth query 
							if(ar2.succeeded()){  
								logger.info("insert_role_perms,新增数量为:" + ar2.result().rowCount());
								promise.complete(ar2.result());
					        }else{
					        	logger.info("insert_role_perms,失败,失败原因:" + ar2.cause().getMessage());	
					        	promise.fail(ar2.cause());
					        }
						})) 
				// Commit the transaction	
				.compose(res3 -> tx.commit() ) )
				// Return the connection to the pool
			    .eventually(v -> conn.close())
			    .onSuccess(v -> logger.info("delete_role_menus,insert_role_menus,delete_role_perms and insert_role_perms, Transaction succeeded"))
			    .onFailure(err -> logger.info("delete_role_menus,insert_role_menus,delete_role_perms and insert_role_perms, Transaction failed: " + err.getMessage()));   
	    return promise.future();
	}
	
	 

}
